﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtility;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
namespace OracleDAL.Sec
{
    public class UserGroup
    {  
        /// <summary>
        /// 删除用户组,用户组权限
        /// </summary>
        /// <param name="UserGroupISN"></param>
        /// <returns></returns>
        public bool Delete(string UserGroupISN)
        {
            List<string> tranSql = new List<string>();
            //删除用户组
            string sql = "DELETE FROM SEC_USERGROUP WHERE USERGROUPISN='" + UserGroupISN + "'";
            //删除用户组权限
            string delUserGroup_operate = "delete from sec_usergroup_operate where usergroupisn='" + UserGroupISN + "'";
            //删除用户组与用户的对应信息,因为在页面有提示，必须先转移用户
            //string delUserGroup_UserInfo = "delete from SEC_USERGROUP_USERINFO where usergroupisn='" + UserGroupISN + "'";
            tranSql.Add(sql);
            tranSql.Add(delUserGroup_operate);
            //tranSql.Add(delUserGroup_UserInfo);
            int effectRows = DBUtility.OracleHelper.ExecuteSqlTran(tranSql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        public bool Update(Model.Sec.UserGroup userGroup)
        {
            string sql = "update SEC_USERGROUP set USERGROUPNAME='" + userGroup.UserGroupName + "',Description='" + userGroup.Description + "',SortIndex='" + userGroup.SortIndex + "',ISROOTS=" + userGroup.ISROOTS + " where usergroupisn='" + userGroup.UserGroupISN + "'";
            int effectRows = DBUtility.OracleHelper.ExecuteSql(sql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 新增用户组
        /// </summary>
        /// <param name="UserGroupISN">用户组ISN</param>
        /// <param name="UserGroupName">用户组名</param>
        /// <param name="Description">描述</param>
        /// <param name="CreateTime">创建时间</param>
        /// <param name="SortIndex">显示顺序</param>
        /// <returns></returns>
        private bool NewUserGroup(string UserGroupISN, string UserGroupName, string Description, DateTime CreateTime, string SortIndex)
        {
            string sql = "INSERT INTO SEC_USERGROUP (USERGROUPISN,USERGROUPNAME,DESCRIPTION,CREATETIME,SORTINDEX) ";
            sql += "values('" + UserGroupISN + "','" + UserGroupName + "','" + Description + "',to_date('" + CreateTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')," + SortIndex + ")";
            int effectRows = DBUtility.OracleHelper.ExecuteSql(sql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        public bool NewUserGroup(Model.Sec.UserGroup userGroup)
        {
            return this.NewUserGroup(userGroup.UserGroupISN, userGroup.UserGroupName, userGroup.Description, userGroup.CreateTime, userGroup.SortIndex);
        }
        public Model.Sec.UserGroup GetModel(string UserGroupISN)
        {
            string sql = "SELECT * FROM SEC_USERGROUP WHERE USERGROUPISN='" + UserGroupISN + "'";
            System.Data.OracleClient.OracleDataReader odr = DBUtility.OracleHelper.ExecuteReader(sql);
            Model.Sec.UserGroup ug = null;
            if (odr.Read())
            {
                ug = new Model.Sec.UserGroup();
                if(odr["CREATETIME"].ToString()!="")
                ug.CreateTime = Convert.ToDateTime(odr["CREATETIME"]);
                ug.Description = odr["DESCRIPTION"].ToString();
                ug.SortIndex = odr["SORTINDEX"].ToString();
                ug.UserGroupISN = odr["USERGROUPISN"].ToString();
                ug.UserGroupName = odr["USERGROUPNAME"].ToString();
                if (odr["ISROOTS"].ToString() != "")
                {
                    ug.ISROOTS = int.Parse(odr["ISROOTS"].ToString());
                }
                else
                {
                    ug.ISROOTS = 0;
                }
            }
            return ug;
        }
        public List<Model.Sec.UserGroup> GetAll(string SortExpression, string SortDirection, bool isRoots)
        {
            string sql = "SELECT * FROM SEC_USERGROUP ";
            if (isRoots)
            {   
                if (SortExpression.Trim() != "")
                {
                    sql = sql + "order by " + SortExpression + " " + SortDirection;
                }
            }
            else
            {
                sql = sql + " where isRoots='0' ";
                if (SortExpression.Trim() != "")
                {
                    sql = sql + "order by " + SortExpression + " " + SortDirection;
                }
            }
            System.Data.OracleClient.OracleDataReader odr = DBUtility.OracleHelper.ExecuteReader(sql);
            List<Model.Sec.UserGroup> list = new List<Model.Sec.UserGroup>();
            while (odr.Read())
            {
                Model.Sec.UserGroup ug = new Model.Sec.UserGroup();
                if(odr["CREATETIME"].ToString()!="")
                ug.CreateTime = Convert.ToDateTime(odr["CREATETIME"]);
                ug.Description = odr["DESCRIPTION"].ToString();
                ug.SortIndex = odr["SORTINDEX"].ToString();
                ug.UserGroupISN = odr["USERGROUPISN"].ToString();
                ug.UserGroupName = odr["USERGROUPNAME"].ToString();
                if (odr["ISROOTS"].ToString() != "")
                {
                    ug.ISROOTS = int.Parse(odr["ISROOTS"].ToString());
                }
                list.Add(ug);
            }
            return list;
        }
        public Int64 GetCount(string strWhere)
        {
            string sql = "select * from sec_usergroup where " + strWhere;
            Int64 i = 0;
            OracleDataReader dr=OracleHelper.ExecuteReader(sql);
            while (dr.Read())
                i++;
            dr.Close();
            return i;
        }
        /// <summary>
        /// 增加一条数据，给用户组增加OperateID
        /// </summary>
        public bool add(Model.Sec.UserGroup model)
        {
            List<string> tranSql = new List<string>();
            StringBuilder strSql = new StringBuilder();
           
            strSql.Append("insert into SEC_USERGROUP");
            strSql.Append("(USERGROUPISN,USERGROUPNAME,DESCRIPTION,CREATETIME,ISROOTS,SORTINDEX) ");
            strSql.Append(" values('");
            strSql.Append(model.UserGroupISN + "','" + model.UserGroupName + "','" + model.Description + "',to_date('" + model.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd HH24:mi:ss')");
            strSql.Append(",'" + model.ISROOTS + "','" + model.SortIndex + "')");
            //给用户组增加OperateID
            string sql = "insert into sec_usergroup_operate (operateid,usergroupisn,belongsystem,enabled) select distinct(operateid) operateid, '" + model.UserGroupISN + "' usergroupisn,'10' belongsystem, '0' enabled from sec_operate_new where belongsystem='10' ";
            tranSql.Add(strSql.ToString());
            tranSql.Add(sql);
            int rows = OracleHelper.ExecuteSqlTran(tranSql);
           
            if (rows > 0)
                return true;
            else
                return false;

        }
        public Model.Sec.UserGroup[] getBYUser(string userISN)
        {   
            ArrayList arrlist=new ArrayList();
           
            string sql = "select ug.USERGROUPISN,USERGROUPNAME,DESCRIPTION,CREATETIME,ISROOTS,SORTINDEX from SEC_USERGROUP_USERINFO suu,SEC_USERGROUP ug where suu.USERGROUPISN=ug.USERGROUPISN and suu.userisn='" + userISN + "'";
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            while (dr.Read())
            {   
                Model.Sec.UserGroup model=new Model.Sec.UserGroup();
                if (dr["CREATETIME"].ToString() != "")
                    model.CreateTime =Convert.ToDateTime(dr["CREATETIME"].ToString());
                model.Description = dr["DESCRIPTION"].ToString();
                if (dr["ISROOTS"].ToString() != "")
                {
                    model.ISROOTS = int.Parse(dr["ISROOTS"].ToString());
                }
                else
                {
                    model.ISROOTS = 0;
                }
                model.SortIndex = dr["SORTINDEX"].ToString();
                model.UserGroupISN = dr["USERGROUPISN"].ToString();
                model.UserGroupName = dr["USERGROUPNAME"].ToString();
                arrlist.Add(model);
            }
             Model.Sec.UserGroup[] arruserGroup =(Model.Sec.UserGroup[])arrlist.ToArray(typeof(Model.Sec.UserGroup));
            return arruserGroup;
        }
        /// <summary>
        /// 更新用户组权限,如果没有便插入
        /// </summary>
        /// <param name="UserGroupISN"></param>
        /// <param name="OperateIDs"></param>
        /// <param name="Enable"></param>
        /// <returns></returns>
        public bool update(string UserGroupISN, string OperateIDs, string Enable)
        {
            string sql = "update SEC_USERGROUP_OPERATE set enabled='" + Enable + "' where usergroupisn='" + UserGroupISN + "' and operateid  in (" + OperateIDs + ") and belongsystem='10'";
            bool flag = OracleHelper.ExecuteSql(sql) > 0 ? true : false;
            if (!flag)
            {
                List<string> tranSql = new List<string>();
                OperateIDs = OperateIDs.Replace("'", "");
                string[] operate = OperateIDs.Split(',');
                for (int i = 0, len = operate.Length; i < len; i++)
                {
                    string sql1 = "insert into sec_usergroup_operate(USERGROUPISN,OPERATEID,BELONGSYSTEM,ENABLED) values ('" + UserGroupISN + "','" + operate[i] + "','10'," + Enable + ")";
                    tranSql.Add(sql1);
                }
                flag = OracleHelper.ExecuteSqlTran(tranSql) > 0 ? true : false;
            }
            return flag;
        }
        public Model.Sec.UserGroup[] getModels(string strWhere)
        {
            ArrayList arrlist = new ArrayList();
            string sql = "select * from sec_usergroup ";
            if (strWhere.Trim() != "")
                sql += "where " + strWhere;
            sql = sql + "  order by usergroupname";
           OracleDataReader dr= OracleHelper.ExecuteReader(sql);
           while (dr.Read())
           {
               Model.Sec.UserGroup model = new Model.Sec.UserGroup();
               if (dr["CREATETIME"].ToString() != "")
                   model.CreateTime = Convert.ToDateTime(dr["CREATETIME"].ToString());
               model.Description = dr["DESCRIPTION"].ToString();
               if (dr["ISROOTS"].ToString() != "")
               {
                   model.ISROOTS = int.Parse(dr["ISROOTS"].ToString());
               }
               else
               {
                   model.ISROOTS = 0;
               }
               model.SortIndex = dr["SORTINDEX"].ToString();
               model.UserGroupISN = dr["USERGROUPISN"].ToString();
               model.UserGroupName = dr["USERGROUPNAME"].ToString();
               arrlist.Add(model);
           }
           Model.Sec.UserGroup[] arruserGroup = (Model.Sec.UserGroup[])arrlist.ToArray(typeof(Model.Sec.UserGroup));
           return arruserGroup;
        }
        public DataSet getTotalUserGroup()
        {
            DataSet ds = new DataSet();
            string strSql = "select USERGROUPISN as 用户组ISN,USERGROUPNAME as 用户组名称,DESCRIPTION as 描述,CREATETIME as 创建时间 ";
            strSql += " from sec_usergroup";
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;

        }
        public int insertScreen(string usergroupisn, string screen, string rootOperate, string childOperate, string type)
        {
            string sqlstr = "select count(*) from screenmenu where usergroupisn='" + usergroupisn + "'";
            bool record = OracleHelper.Exists(sqlstr);
            string sql = "";
            if (record)
            {
                switch (type)
                {
                    case "1":
                        sql = "update screenmenu set screen1url='" + screen + "',rootoperateid1='" + rootOperate + "',childoperateid1='" + childOperate + "'";
                        sql += " where usergroupisn='" + usergroupisn + "' ";
                        break;
                    case "2":
                        sql = "update screenmenu set screen2url='" + screen + "',rootoperateid2='" + rootOperate + "',childoperateid2='" + childOperate + "'";
                        sql += " where usergroupisn='" + usergroupisn + "' ";
                        break;

                } 
            }
            else
            {

                switch (type)
                {
                    case "1":
                        sql = "insert into screenmenu(usergroupisn,screen1url,rootoperateid1,childoperateid1)";
                        sql += " values ('" + usergroupisn + "','" + screen + "','" + rootOperate + "','" + childOperate + "')";
                        break;
                    case "2":
                        sql = "insert into screenmenu(usergroupisn,screen2url,rootoperateid2,childoperateid2)";
                        sql += " values ('" + usergroupisn + "','" + screen + "','" + rootOperate + "','" + childOperate + "')";
                        break;

                }


             
            }
            return DBUtility.OracleHelper.ExecuteSql(sql);
        }
        public int updateScreen(string usergroupisn, string s1, string s2)
        {
            string sql = "";
            if (s1 == ""&& s2!="")
            {
                sql = "update screenmenu  set screen1url='',rootoperateid1='',childoperateid1='' where usergroupisn='" + usergroupisn + "'";
            }
            else if (s2 == "" && s1 != "")
            {
                sql = "update screenmenu set screen2url='',rootoperateid2='',childoperateid2='' where usergroupisn='" + usergroupisn + "'";
            }
            else if (s2 == "" && s1 == "")
            {
                sql = "delete screenmenu  where usergroupisn='" + usergroupisn + "'";
            }
            else
            {
                return 0;
            }
            return DBUtility.OracleHelper.ExecuteSql(sql);

        }
        public DataSet  setScreen(string usergroup)
        {
            DataSet ds = new DataSet();
            string value = "";
            string sql = "select s.operatename as name2,s1.operatename as name1, c.usergroupisn as isn,c.screen1url as url1,c.screen2url as url2,c.rootoperateid1 as rid1,c.childoperateid1 as cid1,c.rootoperateid2 as rid2,c.childoperateid2 as cid2"; 
                    sql +=" from screenmenu c,sec_operate_new s,sec_operate_new s1";
                    sql += " where s.operateid=c.childoperateid2  ";
                    sql += " and s1.operateid=c.childoperateid1";
                    sql += " and usergroupisn in (" + usergroup+ ")" ; 
            ds = DBUtility.OracleHelper.Query(sql);
     

            return ds;

        }
    }
}
